/*******************************************************************************
*Project :          COVID-19 Research Project 

*Purpose : 		    Generate statistics to be included in paper

*/

clear all 
set more off 
capture log close 

* generate global date
global date=subinstr(c(current_date), " ", "_", .)
set obs 1 
gen date="${date}"
split date, parse("_")
cap replace date=date3+"_"+date2+"_"+date4 if date1==""
cap replace date=date2+"_"+date1+"_"+date3 if date1!=""
global date=date[1]
display as error "Today's date = ${date}"


global stay_at_home "${machine}/data/clean/stay_at_home"
global tables "${machine}/tables"
global aggregate "${machine}/figures/aggregate"

/*******************************************************************************
Check state variation in stay at home orders
*******************************************************************************/

use "${stay_at_home}/stay_at_home_AM.dta", clear
compress

preserve
* put states in to groups 
summ date 
keep if date == `r(max)' 
tab date 
duplicates report county_fips 

* states with no stay_at_home orders 
bys state_fips : egen max_stay = max(stay_at_home) 
tab max_stay 
g no_sah  = 1 if max_stay == 0 
tab state if no_sah  == 1 

* states with only state-wide order 
egen unique_dates = nvals(effective_date) , by(state_fips)
tab unique_dates 
g only_st_sah = 1 if unique_dates == 1 
tab state if only_st_sah == 1 

* states with some counties with an order 
bys state_fips : gen no_counties = _N 
bys state_fips : egen sum_sah = total(stay_at_home) 
g frac_sah = sum_sah/no_counties
tab frac_sah 
g some_ct_sah = 1 if frac_sah!=0 & frac_sah!=1 
tab state if some_ct_sah == 1


* states in which some counties announced an order before state-wide 
g heter_sah = 1 if frac_sah == 1 & unique_dates>1 
tab state if heter_sah == 1

bys state_fips : keep if _n ==1 
keep state_fips state  no_sah only_st_sah some_ct_sah heter_sah

* sanity test 
foreach var in  no_sah only_st_sah some_ct_sah heter_sah { 
	replace `var' = 0 if missing(`var')
	} 

g st_group = 1 if        only_st_sah == 1 
replace st_group = 2 if  heter_sah == 1 
replace st_group = 3 if  some_ct_sah == 1 
replace st_group = 4 if  no_sah == 1 
tab st_group 

	*27 states (plus DC) with only state stay-at-home orders
	*16 with county variation before state sah orders
	*3 states with only county sah (which makes 19 subject to county variation)
	*5 states with no action 

keep if heter_sah==1
tempfile varied_sah
save `varied_sah'
restore

merge m:1 state using `varied_sah'
keep if _merge==3
sort county_fips date
bys county_fips: keep if _n==1

bys state_fips: egen state_eff_date=median(effective_date)
format state_eff_date %td
*br state effective_date state_eff_date
gen tag=1 if effective_date<state_eff_date
tab tag
	*153 counties that had stay at home orders before their state


/*******************************************************************************
Coverage of stay-at-home orders
*******************************************************************************/

u "${stay_at_home}/stay_at_home_maps.dta", clear

unique county_fips if stay_at_home==1
	*2,642 counties covered by stay at home order over our sample

u "${stay_at_home}/stay_at_home_forR.dta", clear

keep if date==td(07apr2020)
collapse (sum) tot_pop, by(stay_at_home)
di (tot_pop[2])/(tot_pop[1]+tot_pop[2])
	*96% of people covered by stay at home orders

/*******************************************************************************
Large business spending make-up, by sector (Second Measure)
*******************************************************************************/

import excel using "${tables}/summary/summary_second_measure_consumer.xlsx", cellra(A3:F25) firstrow case(lower) clear

di avg[15]/avg[22]
	*43.6% of spending is retail
di (avg[22]-(avg[15]+avg[21]+avg[6]))/avg[22]
	*49.7% of spending is non-retail
di avg[21]/avg[22]
	*5% of spending is Amazon.com
di avg[6]/avg[22]
	*1.7% of spending is food delivery

/*******************************************************************************
Small business spending breakdown
*******************************************************************************/

import excel using "${tables}/summary/summary_womply_total.xlsx", cellra(A3:F25) firstrow case(lower) clear

di avg[18]+avg[19]+avg[20]+avg[21]
	*$55,416 of revenue from restaurants in Womply panel on average by county
di (avg[18]+avg[19]+avg[20]+avg[21])/avg[22]
	*17% of revenue is from restaurants

/*******************************************************************************
Aggregate percent changes, year over year
*******************************************************************************/

u "${aggregate}/aggregate.dta", clear
format date %td

preserve
keep if date==td(17apr2020)

di gr_rev_All[1]
	*Womply overall spending fell by 37.39% by April 17, 2020
di gr_sales_All[1]
	*Second Measure overall spending rose by 9.04% by April 17, 2020
di gr_sales_Retail[1]
	*Second Measure Brick & Mortar spending fell by 8.54% by April 17, 2020
di gr_sales_Online[1]
	*Second Measure online spending rose by 80.07% by April 17, 2020
di daily_distance_diff[1]
	*Total distance traveled fell by 32.96% by April 17, 2020
di daily_visitation_diff[1]
	*Visits to non-essential businesses fell by 50.73% by April 17, 2020
restore

keep if (date>=td(11mar2020) & date<=td(17apr2020))

di daily_distance_diff[7]-daily_distance_diff[1]
	*Distance traveled dropped by 7.9% between March 11 and March 17
di daily_visitation_diff[7]-daily_visitation_diff[1]
	*Non-essential visits dropped by 6.5% between March 11 and March 17
di daily_distance_diff[38]-daily_distance_diff[7]
	*Distance traveled dropped an additional 24% between March 17 and April 17
di daily_visitation_diff[38]-daily_visitation_diff[7]
	*Non-essential visits dropped by an additional 40% between March 17 and April 17

u "${aggregate}/amazon_others.dta", clear
format date %td
keep if date==td(17apr2020)

di gr_sales_7[1]
	*Spending on food delivery services increased by 148.82% by April 17
di gr_sales_2[1]
	*Spending on Amazon.com increased by 72.66% by April 17

